<?php

function list_employment_count($periode_id){
	if( $periode_id==0 ){
		$periode_id=get_periode_absen();
	}		
	$periodes = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$tanggal=$periodes['tanggal'] ;
	$bulan = (int) date('m' , strtotime($tanggal));
	$tahun = (int) date('Y' , strtotime($tanggal));
	$tgl=get_last_date_by_month( $tahun, $bulan);

	my_set_code_js('	
		function postDataPeriode(periode_id){ 
			location.href=\'index.php?com='.$_GET['com'].'&task=list&periode_id=\'+periode_id ;
		}
	');
	$view =  form_header( "Employement" , "Count"  ); 
	$tab_options = array(
		'I'=>'tab0',
		'II'=>'tab1',
		'III'=>'tab2' ,
		'IV'=>'tab3' ,
		'V'=>'tab4', 
		'VI'=>'tab5' 
	);
	$contents = array(
		'tab0'=>list_profesi($tgl) ,
		'tab1'=>list_status_karyawan($tgl) ,
		'tab2'=>list_status_perkawinan($tgl) ,
		'tab3'=>list_golongan($tgl) ,
		'tab4'=>list_usia($tgl) ,
		'tab5'=>list_pendidikan($tgl) ,
	);
	$view = tab_page($tab_options , $contents );
	
	$menuontop = array(  
		'Cetak' => array('onclick'=>'javascript:window.open(\'index.php?com='.$_GET['com'].'&task=plaincetak&tgl='.$tgl.'\',\'mywin\',\'left=20,top=20,width=800,height=600,toolbar=0,scrollbars,resizable=0\');'),
		'Excel'=>array('onclick'=>'javascript:location.href=\'index.php?com='.$_GET['com'].'&task=excel&tgl='.$tgl.'\';'),
	);
	$query = " SELECT * FROM pr_periode ORDER BY periode_id DESC";
	$result = my_query($query);
	$prs = "";
	while($prow = my_fetch_array($result)){
		if($prow['periode_id'] ==$periode_id){
			$prs .='<option selected value="'.$prow['periode_id'].'">'.date('m-Y',strtotime($prow['tanggal'])).'</option>';
		}else{
			$prs .='<option value="'.$prow['periode_id'].'">'.date('m-Y',strtotime($prow['tanggal'])).'</option>';
		}	
	}
	$search = '<form method="GET"><input type="hidden" name="com" value="'.$_GET['com'].'"/>
	<table width="100%">
	<tr>
		<td>Periode</td>	 
		<td><select name="periode_id" id="periode_id" onchange="javascript:postDataPeriode(this.value)">'.$prs.'</select></td>				
	</tr>
	</table>
	</form>';
	$printmenu = additional_menu_on_list( $menuontop , $search);
	return $printmenu .$view ;
}


function list_profesi($tgl){
	$header = array(
		'URAIAN'=>array('style'=>'border-bottom:2px solid;width:75%'),   
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		' '=>array('style'=>'text-align:right;border-bottom:2px solid;width:5%'), 
	);
	$query = "SELECT a.nama_profesi,a.kelompok,a.profesi_id, (select COUNT(*) FROM kekaryawanan b 
		WHERE b.tmb<'{$tgl}' 
		AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		AND b.profesi_id=a.profesi_id)  as total 
		FROM profesi a ORDER BY a.kelompok,a.profesi_id"; 
	$result = my_query($query);
	$row = array();
	$group='';
	$kelompok='Kesehatan';
	$subtotal=0;
	while( $ey = my_fetch_array($result) ){
		$detailproperty = array(
			'href'=>'index.php?com='.$_GET['com'].'&task=detail&tab=1&id='.$ey['profesi_id'],
			'title'=>'Daftar Karyawan'
		);
		$detail_button = button_icon( 'b_props.png' , $detailproperty  );
		if( $ey['kelompok']<>$kelompok ){
			$row[] = array(
				'nama'=> '<b>&nbsp;&nbsp;&nbsp;&nbsp;Sub Total</b>',
				'total'=>'<b>'.position_text_align( $subtotal , 'right').'</b>',  
				'operasi'=> ' ', 
			);		
			$kelompok=$ey['kelompok'] ;
		}	
		$subtotal+= $ey['total']  ;
		$atemps = explode( "-",$ey['nama_profesi']);
		if( count($atemps)>1 ){
			if( $group=='' OR $group<>$atemps[0]){
				$group=$atemps[0];
				$row[] = array(
					'nama'=> $group,
					'total'=> ' ',
					'operasi'=> ' ', 
				);
			}	
			$profesi='&nbsp;'.$atemps[1] ;
		}else{
			$profesi=$ey['nama_profesi'] ;
		}
		$row[] = array(
			'nama'=> $profesi,
			'total'=>position_text_align( $ey['total'] , 'right'), 
			'operasi'=> position_text_align( $detail_button, 'right'),  
		);
	}
	$row[] = array(
		'nama'=> '&nbsp;&nbsp;&nbsp;&nbsp;<b>TOTAL</b>',
		'total'=>'<b>'.position_text_align( $subtotal , 'right').'</b>',  
		'operasi'=> ' ', 
	);			
	$datas = table_rows_plain($row); 
	$view = "I. BERDASARKAN PROFESI";
	return $view.table_builder_plain($header , $datas ,  6 ,false    );
}

function list_status_karyawan($tgl){
	$header = array(
		'STATUS KARYAWAN'=>array('style'=>'border-bottom:2px solid;width:35%'),   
		'LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		' '=>array('style'=>'text-align:right;border-bottom:2px solid;width:5%'), 
	);
	$query = "SELECT d.tetap,c.kelamin,COUNT(*) as total FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id=a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id=b.biodata_id  
		INNER JOIN status_kekaryawanan d ON d.status_id=a.status_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		GROUP BY d.tetap,c.kelamin" ;
	$result = my_query($query);
	$row = array();
	$i=0;
	$tetap_laki=$tetap_perempuan=$tidak_tetap_laki=$tidak_tetap_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['tetap']=="ya"){
			if( $ey['kelamin']=="laki-laki"){
				$tetap_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$tetap_perempuan+=$ey['total'] ;
			}
		}elseif( $ey['tetap']=="tidak"){
			if( $ey['kelamin']=="laki-laki"){
				$tidak_tetap_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$tidak_tetap_perempuan+=$ey['total'] ;
			}
		}
	}
	$detailproperty = array(
		'href'=>'index.php?com='.$_GET['com'].'&task=detail&tab=2&id=1',
		'title'=>'Daftar Karyawan'
	);
	$detail_button = button_icon( 'b_props.png' , $detailproperty  );		
	$row[] = array(
		'status'=> 'Tenaga Tetap',
		'laki'=>position_text_align( $tetap_laki , 'right'), 
		'perempuan'=>position_text_align( $tetap_perempuan , 'right'), 
		'total'=>position_text_align( $tetap_laki+$tetap_perempuan , 'right'), 
		'operasi'=> position_text_align( $detail_button, 'right'),  		
	);
	$detailproperty = array(
		'href'=>'index.php?com='.$_GET['com'].'&task=detail&tab=2&id=2',
		'title'=>'Daftar Karyawan'
	);
	$detail_button = button_icon( 'b_props.png' , $detailproperty  );		
	$row[] = array(
		'status'=> 'Tenaga Tidak Tetap (Kontrak)',
		'laki'=>position_text_align( $tidak_tetap_laki , 'right'), 
		'perempuan'=>position_text_align( $tidak_tetap_perempuan , 'right'), 
		'total'=>position_text_align( $tidak_tetap_laki+$tidak_tetap_perempuan, 'right'), 
		'operasi'=> position_text_align( $detail_button, 'right'),  		
	);
	$row[] = array(
		'status'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $tetap_laki+$tidak_tetap_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $tetap_perempuan+$tidak_tetap_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $tetap_laki+$tidak_tetap_laki+$tetap_perempuan+$tidak_tetap_perempuan , 'right')."</b>", 
		'operasi'=> ' ',  
	);	
	$datas = table_rows_plain($row); 
	$view = "II. BERDASARKAN STATUS KARYAWAN";
	return $view.table_builder_plain($header , $datas ,  6 ,false    );
}


function list_status_perkawinan($tgl){
	$header = array(
		'LAJANG LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'LAJANG PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'MENIKAH LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'MENIKAH PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
	);
	$query = "SELECT c.kelamin,c.marital,COUNT(*) as total FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id=a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id=b.biodata_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		GROUP BY c.kelamin,c.marital" ;
	$result = my_query($query);
	$row = array();
	$i=0;
	$lajang_laki=$lajang_perempuan=$menikah_laki=$menikah_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['marital']=="Belum Nikah"){
			if( $ey['kelamin']=="laki-laki"){
				$lajang_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$lajang_perempuan+=$ey['total'] ;
			}
		}else{
			if( $ey['kelamin']=="laki-laki"){
				$menikah_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$menikah_perempuan+=$ey['total'] ;
			}
		}
	}	
	$row[] = array(
		'lajang_laki'=> position_text_align( $lajang_laki , 'right'), 
		'lajang_perempuan'=>position_text_align( $lajang_perempuan , 'right'), 
		'menikah_laki'=>position_text_align( $menikah_laki , 'right'), 
		'menikah_perempuan'=>position_text_align( $menikah_perempuan , 'right'), 
		'total'=>position_text_align( $lajang_laki+$lajang_perempuan+$menikah_laki+$menikah_perempuan , 'right'), 
	);

	$row[] = array(
		'lajang_laki'=> position_text_align( ' ' , 'right'), 
		'lajang_perempuan'=>"<b>".position_text_align( $lajang_laki+$lajang_perempuan , 'right')."</b>", 
		'menikah_laki'=>position_text_align( ' ' , 'right'), 
		'menikah_perempuan'=>"<b>".position_text_align( $menikah_laki+$menikah_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $lajang_laki+$lajang_perempuan+$menikah_laki+$menikah_perempuan , 'right')."</b>", 
	);
	$datas = table_rows_plain($row); 
	$view = "III. BERDASARKAN STATUS PERKAWINAN";
	return $view.table_builder_plain($header , $datas ,  6 ,false    );
}

function list_golongan($tgl){
	$header = array(
		'PANGKAT / GOLONGAN JABATAN'=>array('style'=>'border-bottom:2px solid;width:35%'),   
		'LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		' '=>array('style'=>'text-align:right;border-bottom:2px solid;width:5%'), 
		);
	$query = "SELECT a.pangkat_id, a.nama_pangkat,
		(SELECT COUNT(*) FROM kekaryawanan b
			INNER JOIN golongan_jabatan c ON c.golongan_jabatan_id=b.golongan_jabatan_id 
			INNER JOIN karyawan d ON d.karyawan_id=b.karyawan_id 
			INNER JOIN biodata e ON e.biodata_id=d.biodata_id  
			WHERE b.tmb<'{$tgl}' 
			AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.pangkat_id=a.pangkat_id 
			AND e.kelamin='laki-laki') as laki,
		(SELECT COUNT(*) FROM kekaryawanan b
			INNER JOIN golongan_jabatan c ON c.golongan_jabatan_id=b.golongan_jabatan_id 
			INNER JOIN karyawan d ON d.karyawan_id=b.karyawan_id 
			INNER JOIN biodata e ON e.biodata_id=d.biodata_id  
			WHERE b.tmb<'{$tgl}' 
			AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.pangkat_id=a.pangkat_id 
			AND e.kelamin='perempuan') as perempuan 
			FROM pangkat a 
			ORDER BY a.pangkat_id";
	$result = my_query($query);
	$row = array();
	$total_laki=$total_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		$total_laki+=$ey['laki'] ;
		$total_perempuan+=$ey['perempuan'] ;
		$detailproperty = array(
			'href'=>'index.php?com='.$_GET['com'].'&task=detail&tab=4&id='.$ey['pangkat_id'],
			'title'=>'Daftar Karyawan'
		);
		$detail_button = button_icon( 'b_props.png' , $detailproperty  );			
		$row[] = array(
			'pangkat'=> $ey['nama_pangkat'],
			'laki'=>position_text_align( $ey['laki'] , 'right'), 
			'perempuan'=>position_text_align( $ey['perempuan'] , 'right'), 
			'total'=>position_text_align( $ey['laki']+$ey['perempuan'] , 'right'), 
			'operasi'=> position_text_align( $detail_button, 'right'),  		
		);
	}
	$row[] = array(
		'pangkat'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $total_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $total_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $total_laki+$total_perempuan , 'right')."</b>", 
		'operasi'=> ' ',  		
	);	
	$datas = table_rows_plain($row); 
	$view = "IV. BERDASARKAN GOLONGAN / PANGKAT";
	return $view.table_builder_plain($header , $datas ,  6 ,false    );
}


function list_usia($tgl){
	$header = array(
		'USIA (Tahun)'=>array('style'=>'border-bottom:2px solid;width:35%'),   
		'LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		' '=>array('style'=>'text-align:right;border-bottom:2px solid;width:5%'), 
	);
	$total_laki=$total_perempuan=0;
	for( $i=20; $i<=50; $i+=5 ){
		if($i==20){
			$start=0 ;
			$label1='< ' ;
		}else{
			$start=$i ;
			$label1='> ' ;
		}
		if($i==50){
			$end=150 ;
			$label2=$i.' >' ;
		}else{
			$end=$i+5 ;
			$label2=($i+5) ;
		}	
		$laki=get_total_by_gender_age($tgl,'laki-laki', $start,$end);
		$perempuan=get_total_by_gender_age($tgl,'perempuan', $start,$end);
		$total_laki+=$laki ;
		$total_perempuan+=$perempuan ;
		$detailproperty = array(
			'href'=>'index.php?com='.$_GET['com'].'&task=detail&tab=5&id='.$i,
			'title'=>'Daftar Karyawan'
		);
		$detail_button = button_icon( 'b_props.png' , $detailproperty  );			
		$row[] = array(
			'pangkat'=> $label1.$i.' - '.$label2,
			'laki'=>position_text_align( $laki , 'right'), 
			'perempuan'=>position_text_align($perempuan , 'right'), 
			'total'=>position_text_align( $laki+$perempuan , 'right'), 
			'operasi'=> position_text_align( $detail_button, 'right'),  		
		);		
	}
	
	$total=$total_laki+$total_perempuan ;
	$row[] = array(
		'pangkat'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $total_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $total_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $total , 'right')."</b>", 
		'operasi'=> ' ',  	
	);	
	if( $total>0 ){
		$row[] = array(
			'pangkat'=> " ",
			'laki'=>"<b>".position_text_align( round(($total_laki/$total)*100,2).' %' , 'right')."</b>", 
			'perempuan'=>"<b>".position_text_align( round(($total_perempuan/$total)*100,2).' %' , 'right')."</b>", 
			'total'=>" ", 
			'operasi'=> ' ',  	
		);	
	}
	$datas = table_rows_plain($row); 
	$view = "V. BERDASARKAN USIA";
	return $view.table_builder_plain($header , $datas ,  6 ,false    );
}

function get_total_by_gender_age($tgl,$gender,$a,$b){
	$query = "SELECT COUNT( * ) AS TRT
			FROM kekaryawanan a
			INNER JOIN karyawan b ON b.karyawan_id = a.karyawan_id 
			INNER JOIN biodata c ON c.biodata_id = b.biodata_id
			WHERE a.tmb<'{$tgl}' 
			AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.kelamin = '{$gender}'
			AND (
			(YEAR( CURDATE( ) ) - YEAR( c.tanggal_lahir )) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( c.tanggal_lahir, 5 ) )
			BETWEEN {$a} AND {$b}
			) 
		";

	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['TRT'];
}


function list_pendidikan($tgl){
	$header = array(
		'PENDIDIKAN'=>array('style'=>'border-bottom:2px solid;width:35%'),   
		'LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'),   
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		' '=>array('style'=>'text-align:right;border-bottom:2px solid;width:5%'), 
	);
	
	$query = "
		SELECT a.code, a.level_pendidikan_id , SUM(
			(SELECT COUNT(h.karyawan_id) 
			FROM (
				SELECT b.karyawan_id,d.kelamin, MAX( g.level_pendidikan_id ) AS	LEVEL FROM kekaryawanan b
				INNER JOIN karyawan c ON c.karyawan_id = b.karyawan_id
				INNER JOIN biodata d ON d.biodata_id = c.biodata_id
				INNER JOIN riwayat_pendidikan_karyawan e ON e.karyawan_id = b.karyawan_id
				INNER JOIN riwayat_pendidikan f ON f.riwayat_pendidikan_id = e.riwayat_pendidikan_id
				INNER JOIN pendidikan g ON g.pendidikan_id = f.pendidikan_id
				WHERE b.tmb<'{$tgl}' 
				AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
				GROUP BY b.karyawan_id
				) AS h
				WHERE h.level = a.level_pendidikan_id
				AND  h.kelamin='laki-laki')) as laki,
			SUM(	
			(SELECT COUNT(h.karyawan_id) 
			FROM (
				SELECT b.karyawan_id,d.kelamin, MAX( g.level_pendidikan_id ) AS	LEVEL FROM kekaryawanan b
				INNER JOIN karyawan c ON c.karyawan_id = b.karyawan_id
				INNER JOIN biodata d ON d.biodata_id = c.biodata_id
				INNER JOIN riwayat_pendidikan_karyawan e ON e.karyawan_id = b.karyawan_id
				INNER JOIN riwayat_pendidikan f ON f.riwayat_pendidikan_id = e.riwayat_pendidikan_id
				INNER JOIN pendidikan g ON g.pendidikan_id = f.pendidikan_id
				WHERE b.tmb<'{$tgl}' 
				AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
				GROUP BY b.karyawan_id
				) AS h
				WHERE h.level = a.level_pendidikan_id
				AND  h.kelamin='perempuan')) as perempuan 		
		FROM level_pendidikan a	
		GROUP BY a.code ORDER BY a.level_pendidikan_id DESC ";
	$result = my_query($query);
	$row = array();
	$total_laki=$total_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		$total_laki+=$ey['laki'] ;
		$total_perempuan+=$ey['perempuan'] ;
		$detailproperty = array(
			'href'=>'index.php?com='.$_GET['com'].'&task=detail&tab=6&id='.$ey['level_pendidikan_id'],
			'title'=>'Daftar Karyawan'
		);
		$detail_button = button_icon( 'b_props.png' , $detailproperty  );				
		$row[] = array(
			'level'=> $ey['code'],
			'laki'=>position_text_align( $ey['laki'] , 'right'), 
			'perempuan'=>position_text_align( $ey['perempuan'] , 'right'), 
			'total'=>position_text_align( $ey['laki']+$ey['perempuan'] , 'right'), 
			'operasi'=> position_text_align( $detail_button, 'right'),  
		);
	}
	$row[] = array(
		'level'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $total_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $total_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $total_laki+$total_perempuan , 'right')."</b>", 
		'operasi'=> ' ',  				
	);	
	$datas = table_rows_plain($row); 
	$view = "VI. BERDASARKAN PENDIDIKAN";
	return $view.table_builder_plain($header , $datas ,  6 ,false    );
}

//===========================================================================

function list_karyawan($tgl,$tab,$id){
	$header = array(
		'#'=>array('style'=>'border-bottom:2px solid;width:5%'), 
		'nik'=>array('style'=>'text-align:center;border-bottom:2px solid;width:10%'),   
		'nama'=>array('style'=>'border-bottom:2px solid;width:65%'),  
		'l/p'=>array('style'=>'border-bottom:2px solid;width:20%'), 		
	);

	$query = "
		SELECT a.karyawan_id FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id = a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id = b.biodata_id
		LEFT JOIN status_kekaryawanan d ON d.status_id=a.status_id 
		LEFT JOIN golongan_jabatan e ON e.golongan_jabatan_id=a.golongan_jabatan_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') ";
	
	if(isset($_GET['key'])){		
		$query .= " AND (c.nama LIKE '%{$_GET['key']}%' OR b.nik ='{$_GET['key']}' )";
	}	
	if(isset($_GET['kjk']) AND $_GET['kjk']<>'0'){		
		$query .= " AND left(c.kelamin,1)='{$_GET['kjk']}'";
	}		
	switch($tab){	
		case 1 :
			$atitle=my_get_data_by_id('profesi','profesi_id',$id);
			$title=$atitle['nama_profesi'];
			$query .= " AND a.profesi_id={$id}";
			break;	
		case 2 :
			$title="Tenaga ".($id==1 ? "Tetap" : "Tidak Tetap (Kontrak)");
			$query .= " AND d.tetap='".($id==1 ? "ya" : "tidak")."'";
			break;		
		case 4 :
			$atitle=my_get_data_by_id('pangkat','pangkat_id',$id);
			$title=$atitle['nama_pangkat'];
			$query .= " AND e.pangkat_id={$id}";
			break;	
		case 5 :
			if($id==20){
				$start=0 ;
				$label1='< ' ;
			}else{
				$start=$id ;
				$label1='> ' ;
			}
			if($id==50){
				$end=150 ;
				$label2=$id.' >' ;
			}else{
				$end=$id+5 ;
				$label2=($id+5) ;
			}			
			$title="Usia ".$label1.$id.' - '.$label2;
			$query .= " AND ((YEAR( CURDATE( ) ) - YEAR( c.tanggal_lahir )) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( c.tanggal_lahir, 5 ) )
			BETWEEN {$start} AND {$end})";
			break;	
		case 6 :
			$atitle=my_get_data_by_id('level_pendidikan','level_pendidikan_id',$id);
			$title=$atitle['code'];
			$query .= " AND (SELECT MAX(level_pendidikan_id ) FROM pendidikan f
				INNER JOIN riwayat_pendidikan g ON g.pendidikan_id = f.pendidikan_id			
				INNER JOIN riwayat_pendidikan_karyawan h ON h.riwayat_pendidikan_id = g.riwayat_pendidikan_id 
				WHERE h.karyawan_id = a.karyawan_id)={$id}"; 

			break;			
	}
	$query .= " ORDER BY b.nik";		
	$result = my_query($query );
	$total_records = my_num_rows($result );
	$scroll_page =5;  
	$per_page =15;  
	$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 1 ; 
	if($current_page < 1){
		$current_page = 1;
	}		 
	$pager_url  ="index.php?com={$_GET['com']}&task={$_GET['task']}&tab={$_GET['tab']}&id={$_GET['id']}&kjk={$_GET['kjk']}&key={$_GET['key']}&page=";	 
	$inactive_page_tag = 'style="padding:4px;background-color:#BBBBBB"';  
	$previous_page_text = ' Mundur '; 
	$next_page_text = ' Maju ';  
	$first_page_text = ' Awal '; 
	$last_page_text = ' Akhir ';
	
	$kgPagerOBJ = new kgPager();
	$kgPagerOBJ->pager_set(
		$pager_url, 
		$total_records, 
		$scroll_page, 
		$per_page, 
		$current_page, 
		$inactive_page_tag, 
		$previous_page_text, 
		$next_page_text, 
		$first_page_text, 
		$last_page_text ,$pager_url_last); 
	 		
	$result = my_query($query ." LIMIT ".$kgPagerOBJ->start.", ".$kgPagerOBJ->per_page); 	
	$row = array(); 
	$i = ($current_page  - 1 ) * 15; 
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$row[] = array(
			'#'=>position_text_align ($i, 'center'),
			'nik'=>position_text_align( $karyawan['nik'], 'center'),
			'nama'=>$karyawan['nama_gelar'],  
			'jk'=>strtoupper(substr($karyawan['kelamin'],0,1)),  
		);
	}
	
	$datas = table_rows($row); 
	$paging = $kgPagerOBJ ->showPaging();
	$temp=array();
	$temp['L']="L" ;
	$temp['P']="P" ;
	$form_kjk = array(
		'name'=>'kjk',
		'id'=>'kjk',
		'value'=>isset($_GET['kjk']) ? $_GET['kjk'] : '' 
	);		
	$search = '<form method="GET"><input type="hidden" name="com" value="'.$_GET['com'].'" />
		<input type="hidden" name="task" value="'.$_GET['task'].'" />
		<input type="hidden" name="tab" value="'.$_GET['tab'].'" />
		<input type="hidden" name="id" value="'.$_GET['id'].'" />
		<span style="font:10px verdana"><b>Pencarian cepat</b></span><br/>
		<input type="text"  style="width:150px;" name="key" value="'.(isset($_GET['key']) ? $_GET['key']: "").'" />
		<span style="font:10px verdana"><b>  L/P  &nbsp;</b></span>'.form_dropdown($form_kjk , $temp).'
		<input class="simple_search" type="submit" value=" CARI " /> </form>';
	$menuontop = array(  
		'Kembali'=>array('onclick'=>'javascript:location.href=\'index.php?com='.$_GET['com'].'\''),
		'Export to excel'=>array('onclick'=>'javascript:location.href=\'index.php?com='.$_GET['com'].'&task=excel_daftar_kekaryawan&tab='.$tab.'&id='.$id.'&kjk='.$_GET['kjk'].'&key='.$_GET['key'].'\''),
	);
	
	$view = form_header( " " , " "  );
	$view .=$title;
	$view .= form_footer( );	
	$printmenu = additional_menu_on_list($menuontop, $search);
	return $view.$printmenu.table_builder($header , $datas ,  15 ,false , $paging  );
} 


function excel_daftar_kekaryawan($tgl,$tab,$id){

	my_component_load('xl_builder' , false);
	$header = array(
		'#'=>array('style'=>'border-bottom:2px solid;width:5%'), 
		'nik'=>array('style'=>'text-align:center;border-bottom:2px solid;width:10%'),   
		'nama'=>array('style'=>'border-bottom:2px solid;width:65%'),  
		'l/p'=>array('style'=>'border-bottom:2px solid;width:20%'), 		
	);

	$query = "
		SELECT a.karyawan_id FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id = a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id = b.biodata_id
		LEFT JOIN status_kekaryawanan d ON d.status_id=a.status_id 
		LEFT JOIN golongan_jabatan e ON e.golongan_jabatan_id=a.golongan_jabatan_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') ";
	
	if(isset($_GET['key'])){		
		$query .= " AND (c.nama LIKE '%{$_GET['key']}%' OR b.nik ='{$_GET['key']}' )";
	}	
	if(isset($_GET['kjk']) AND $_GET['kjk']<>'0'){		
		$query .= " AND left(c.kelamin,1)='{$_GET['kjk']}'";
	}		
	switch($tab){	
		case 1 :
			$atitle=my_get_data_by_id('profesi','profesi_id',$id);
			$title=$atitle['nama_profesi'];
			$query .= " AND a.profesi_id={$id}";
			break;	
		case 2 :
			$title="Tenaga ".($id==1 ? "Tetap" : "Tidak Tetap (Kontrak)");
			$query .= " AND d.tetap='".($id==1 ? "ya" : "tidak")."'";
			break;		
		case 4 :
			$atitle=my_get_data_by_id('pangkat','pangkat_id',$id);
			$title=$atitle['nama_pangkat'];
			$query .= " AND e.pangkat_id={$id}";
			break;	
		case 5 :
			if($id==20){
				$start=0 ;
				$label1='< ' ;
			}else{
				$start=$id ;
				$label1='> ' ;
			}
			if($id==50){
				$end=150 ;
				$label2=$id.' >' ;
			}else{
				$end=$id+5 ;
				$label2=($id+5) ;
			}			
			$title="Usia ".$label1.$id.' - '.$label2;
			$query .= " AND ((YEAR( CURDATE( ) ) - YEAR( c.tanggal_lahir )) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( c.tanggal_lahir, 5 ) )
			BETWEEN {$start} AND {$end})";
			break;	
		case 6 :
			$atitle=my_get_data_by_id('level_pendidikan','level_pendidikan_id',$id);
			$title=$atitle['code'];
			$query .= " AND (SELECT MAX(level_pendidikan_id ) FROM pendidikan f
				INNER JOIN riwayat_pendidikan g ON g.pendidikan_id = f.pendidikan_id			
				INNER JOIN riwayat_pendidikan_karyawan h ON h.riwayat_pendidikan_id = g.riwayat_pendidikan_id 
				WHERE h.karyawan_id = a.karyawan_id)={$id}"; 

			break;			
	}
	$query .= " ORDER BY b.nik";		
	$result = my_query($query );
	$row = array();  
	$i=0;
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$row[] = array(
			'#'=>position_text_align ($i, 'center'),
			'nik'=>position_text_align( $karyawan['nik'], 'center'),
			'nama'=>$karyawan['nama_gelar'],  
			'jk'=>strtoupper(substr($karyawan['kelamin'],0,1)),  
		);
	}
	
	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas , 15 ,false ); 
}


function excel_report($tgl){
	my_component_load('xl_builder' , false);
	$header = array(
		'URAIAN'=>array('style'=>'border-bottom:2px solid;width:40%'),   
		'LAKI-LAKI'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		'PEREMPUAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
		'JUMLAH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:20%'), 
	);
	$row = array();

// I
	$query = "SELECT a.nama_profesi,a.kelompok,a.profesi_id, (select COUNT(*) FROM kekaryawanan b 
		WHERE b.tmb<'{$tgl}' 
		AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		AND b.profesi_id=a.profesi_id)  as total 
		FROM profesi a ORDER BY a.kelompok,a.profesi_id"; 
	$result = my_query($query);
	$row[] = array(
		'ur'=> '<b>I. BERDASARKAN PROFESI</b>',
		'lk'=> ' ', 
		'pr'=> ' ', 
		'total'=>' ', 
		);
	$group='';
	$kelompok='Kesehatan';
	$subtotal=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['kelompok']<>$kelompok ){
			$row[] = array(
				'nama'=> '<b>&nbsp;&nbsp;&nbsp;&nbsp;Sub Total</b>',
				'lk'=> ' ', 
				'pr'=> ' ', 				
				'total'=>'<b>'.position_text_align( $subtotal , 'right').'</b>',  
			);		
			$kelompok=$ey['kelompok'] ;
		}	
		$subtotal+= $ey['total']  ;
		$atemps = explode( "-",$ey['nama_profesi']);
		if( count($atemps)>1 ){
			if( $group=='' OR $group<>$atemps[0]){
				$group=$atemps[0];
				$row[] = array(
					'nama'=> $group,
					'lk'=> ' ', 
					'pr'=> ' ', 				
					'total'=> ' ',
				);
			}	
			$profesi='&nbsp;'.$atemps[1] ;
		}else{
			$profesi=$ey['nama_profesi'] ;
		}
		$row[] = array(
			'nama'=> $profesi,
					'lk'=> ' ', 
					'pr'=> ' ', 				
			'total'=>position_text_align( $ey['total'] , 'right'), 
		);	
	}
	$row[] = array(
		'nama'=> '&nbsp;&nbsp;&nbsp;&nbsp;<b>TOTAL</b>',
		'lk'=> ' ', 
		'pr'=> ' ', 				
		'total'=>'<b>'.position_text_align( $subtotal , 'right').'</b>',  
	);	
// II
	$query = "SELECT d.tetap,c.kelamin,COUNT(*) as total FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id=a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id=b.biodata_id  
		INNER JOIN status_kekaryawanan d ON d.status_id=a.status_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		GROUP BY d.tetap,c.kelamin" ;
	$result = my_query($query);
	$row[] = array(
		'ur'=> '<b>II. BERDASARKAN STATUS KARYAWAN</b>',
		'lk'=> ' ', 
		'pr'=> ' ', 
		'total'=>' ', 
		);
	
	$tetap_laki=$tetap_perempuan=$tidak_tetap_laki=$tidak_tetap_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['tetap']=="ya"){
			if( $ey['kelamin']=="laki-laki"){
				$tetap_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$tetap_perempuan+=$ey['total'] ;
			}
		}elseif( $ey['tetap']=="tidak"){
			if( $ey['kelamin']=="laki-laki"){
				$tidak_tetap_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$tidak_tetap_perempuan+=$ey['total'] ;
			}
		}
	}
	$row[] = array(
		'ur'=> 'Tenaga Tetap',
		'lk'=>position_text_align( $tetap_laki , 'right'), 
		'pr'=> position_text_align( $tetap_perempuan , 'right'), 
		'total'=>position_text_align( $tetap_laki+$tetap_perempuan , 'right'), 
	);	
	$row[] = array(
		'ur'=> 'Tenaga Tidak Tetap (Kontrak)',
		'lk'=>position_text_align( $tidak_tetap_laki , 'right'), 
		'pr'=> position_text_align( $tidak_tetap_perempuan , 'right'), 
		'total'=>position_text_align( $tidak_tetap_laki+$tidak_tetap_perempuan, 'right'), 
	);		
	$row[] = array(
		'ur'=> "<b>JUMLAH</b>",
		'lk'=>"<b>".position_text_align( $tetap_laki+$tidak_tetap_laki , 'right')."</b>", 
		'pr'=>"<b>".position_text_align( $tetap_perempuan+$tidak_tetap_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $tetap_laki+$tidak_tetap_laki+$tetap_perempuan+$tidak_tetap_perempuan , 'right')."</b>", 
	);	

// III
	$row[] = array(
		'ur'=> "<b>III. BERDASARKAN STATUS PERKAWINAN</b>",
		'lk'=> ' ', 
		'pr'=> ' ', 
		'total'=>' ', 
	);
		
	$query = "SELECT c.kelamin,c.marital,COUNT(*) as total FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id=a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id=b.biodata_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		GROUP BY c.kelamin,c.marital" ;		
	$result = my_query($query);
	$lajang_laki=$lajang_perempuan=$menikah_laki=$menikah_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['marital']=="Belum Nikah"){
			if( $ey['kelamin']=="laki-laki"){
				$lajang_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$lajang_perempuan+=$ey['total'] ;
			}
		}else{
			if( $ey['kelamin']=="laki-laki"){
				$menikah_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$menikah_perempuan+=$ey['total'] ;
			}
		}
	}
	$row[] = array(
		'ur'=> 'Lajang',
		'lk'=>position_text_align( $lajang_laki , 'right'), 
		'pr'=> position_text_align( $lajang_perempuan , 'right'), 
		'total'=>position_text_align( $lajang_laki+$lajang_perempuan , 'right'), 
	);	
	$row[] = array(
		'ur'=> 'Menikah',
		'lk'=>position_text_align( $menikah_laki , 'right'), 
		'pr'=> position_text_align( $menikah_perempuan , 'right'), 
		'total'=>position_text_align( $menikah_laki+$menikah_perempuan , 'right'), 
	);	
	$row[] = array(
		'ur'=> '<b>Total</b>',
		'lk'=>"<b>".position_text_align( $lajang_laki+$menikah_laki , 'right')."</b>", 
		'pr'=> "<b>".position_text_align( $lajang_perempuan+$menikah_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $lajang_laki+$lajang_perempuan+$menikah_laki+$menikah_perempuan , 'right')."</b>", 
	);

// IV
	$row[] = array(
		'ur'=> "<b>IV. BERDASARKAN GOLONGAN / PANGKAT</b>",
		'lk'=> ' ', 
		'pr'=> ' ', 
		'total'=>' ', 
	);
	$query = "SELECT a.pangkat_id, a.nama_pangkat,
		(SELECT COUNT(*) FROM kekaryawanan b
			INNER JOIN golongan_jabatan c ON c.golongan_jabatan_id=b.golongan_jabatan_id 
			INNER JOIN karyawan d ON d.karyawan_id=b.karyawan_id 
			INNER JOIN biodata e ON e.biodata_id=d.biodata_id  
			WHERE b.tmb<'{$tgl}' 
			AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.pangkat_id=a.pangkat_id 
			AND e.kelamin='laki-laki') as laki,
		(SELECT COUNT(*) FROM kekaryawanan b
			INNER JOIN golongan_jabatan c ON c.golongan_jabatan_id=b.golongan_jabatan_id 
			INNER JOIN karyawan d ON d.karyawan_id=b.karyawan_id 
			INNER JOIN biodata e ON e.biodata_id=d.biodata_id  
			WHERE b.tmb<'{$tgl}' 
			AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.pangkat_id=a.pangkat_id 
			AND e.kelamin='perempuan') as perempuan 
			FROM pangkat a 
			ORDER BY a.pangkat_id";
	$result = my_query($query);
	$total_laki=$total_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		$total_laki+=$ey['laki'] ;
		$total_perempuan+=$ey['perempuan'] ;
		$row[] = array(
			'ur'=> $ey['nama_pangkat'],
			'lk'=>position_text_align( $ey['laki'] , 'right'), 
			'pr'=> position_text_align( $ey['perempuan'] , 'right'), 
			'total'=>position_text_align( $ey['laki']+$ey['perempuan'] , 'right'), 
		);				
	}
	$row[] = array(
		'ur'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $total_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $total_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $total_laki+$total_perempuan , 'right')."</b>", 
	);			
		

// V
	$row[] = array(
		'ur'=> "<b>V. BERDASARKAN USIA</b>",
		'lk'=> ' ', 
		'pr'=> ' ', 
		'total'=>' ', 
	);
	$total_laki=$total_perempuan=0;
	for( $i=20; $i<=50; $i+=5 ){
		if($i==20){
			$start=0 ;
			$label1='< ' ;
		}else{
			$start=$i ;
			$label1='> ' ;
		}
		if($i==50){
			$end=150 ;
			$label2=$i.' >' ;
		}else{
			$end=$i+5 ;
			$label2=($i+5) ;
		}	
		$laki=get_total_by_gender_age($tgl,'laki-laki', $start,$end);
		$perempuan=get_total_by_gender_age($tgl,'perempuan', $start,$end);
		$total_laki+=$laki ;
		$total_perempuan+=$perempuan ;
		
		$row[] = array(
			'pangkat'=> $label1.$i.' - '.$label2,
			'laki'=>position_text_align( $laki , 'right'), 
			'perempuan'=>position_text_align($perempuan , 'right'), 
			'total'=>position_text_align( $laki+$perempuan , 'right'), 
		);		
	}

	$total=$total_laki+$total_perempuan ;
	$row[] = array(
		'pangkat'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $total_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $total_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $total , 'right')."</b>", 
	);	
	if( $total>0 ){
		$row[] = array(
			'pangkat'=> " ",
			'laki'=>"<b>".position_text_align( round(($total_laki/$total)*100,2).' %' , 'right')."</b>", 
			'perempuan'=>"<b>".position_text_align( round(($total_perempuan/$total)*100,2).' %' , 'right')."</b>", 
			'total'=>" ", 
		);	
	}

// VI
	$row[] = array(
		'ur'=> "<b>VI. BERDASARKAN PENDIDIKAN</b>",
		'lk'=> ' ', 
		'pr'=> ' ', 
		'total'=>' ', 
	);	
	$query = "
		SELECT a.code,SUM( 
			(SELECT COUNT(h.karyawan_id) 
			FROM (
				SELECT b.karyawan_id,d.kelamin, MAX( g.level_pendidikan_id ) AS	LEVEL FROM kekaryawanan b
				INNER JOIN karyawan c ON c.karyawan_id = b.karyawan_id
				INNER JOIN biodata d ON d.biodata_id = c.biodata_id
				INNER JOIN riwayat_pendidikan_karyawan e ON e.karyawan_id = b.karyawan_id
				INNER JOIN riwayat_pendidikan f ON f.riwayat_pendidikan_id = e.riwayat_pendidikan_id
				INNER JOIN pendidikan g ON g.pendidikan_id = f.pendidikan_id
				WHERE b.tmb<'{$tgl}' 
				AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
				GROUP BY b.karyawan_id
				) AS h
				WHERE h.level = a.level_pendidikan_id
				AND  h.kelamin='laki-laki')) as laki,
			SUM(
			(SELECT COUNT(h.karyawan_id) 
			FROM (
				SELECT b.karyawan_id,d.kelamin, MAX( g.level_pendidikan_id ) AS	LEVEL FROM kekaryawanan b
				INNER JOIN karyawan c ON c.karyawan_id = b.karyawan_id
				INNER JOIN biodata d ON d.biodata_id = c.biodata_id
				INNER JOIN riwayat_pendidikan_karyawan e ON e.karyawan_id = b.karyawan_id
				INNER JOIN riwayat_pendidikan f ON f.riwayat_pendidikan_id = e.riwayat_pendidikan_id
				INNER JOIN pendidikan g ON g.pendidikan_id = f.pendidikan_id
				WHERE b.tmb<'{$tgl}' 
				AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
				GROUP BY b.karyawan_id
				) AS h
				WHERE h.level = a.level_pendidikan_id
				AND  h.kelamin='perempuan')) as perempuan 		
		FROM level_pendidikan a	
		GROUP BY a.code ORDER BY a.level_pendidikan_id DESC ";
	$result = my_query($query);
	$total_laki=$total_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		$total_laki+=$ey['laki'] ;
		$total_perempuan+=$ey['perempuan'] ;
		$row[] = array(
			'level'=> $ey['code'],
			'laki'=>position_text_align( $ey['laki'] , 'right'), 
			'perempuan'=>position_text_align( $ey['perempuan'] , 'right'), 
			'total'=>position_text_align( $ey['laki']+$ey['perempuan'] , 'right'), 
		);
	}
	$row[] = array(
		'level'=> "<b>JUMLAH</b>",
		'laki'=>"<b>".position_text_align( $total_laki , 'right')."</b>", 
		'perempuan'=>"<b>".position_text_align( $total_perempuan , 'right')."</b>", 
		'total'=>"<b>".position_text_align( $total_laki+$total_perempuan , 'right')."</b>", 
	);				
	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas , 15 ,false ); 
}

function print_plain($tgl){
	$view = '<script>window.print();</script>';	
	$view .='<table style="width:100%" cellspacing="0">';
	$view .='<tr>
			<td align="center"><b>PROFIL SDM</b></td>
			</tr>';
	$view .='<tr>
			<td align="center"><b>PT KRAKATAU MEDIKA</b></td>
			</tr>';
// I			
	$view .='<table style="width:100%" cellspacing="0">
		<tr>
		<td><b>I. BERDASARKAN PROFESI </b></td>
		<td style="text-align:right;width:10%">'.date('d-m-Y', strtotime($tgl)).'</td>
		</tr></table>';	
	$view .='<table style="border:1px solid;width:100%" cellspacing="0">
		<tr>
		<td style="border-bottom:1px solid;text-align:center"><b>URAIAN</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>JUMLAH</b></td>
		</tr>';	
	$query = "SELECT a.nama_profesi,a.kelompok,a.profesi_id, (select COUNT(*) FROM kekaryawanan b 
		WHERE b.tmb<'{$tgl}' 
		AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		AND b.profesi_id=a.profesi_id)  as total 
		FROM profesi a ORDER BY a.profesi_id"; 	
	$result = my_query($query ); 
	$group='';
	$kelompok='Kesehatan';
	$subtotal=0;
	while($ey = my_fetch_array($result)){
		if( $ey['kelompok']<>$kelompok ){
			$view .='<tr>
				<td style="border-top:1px solid;border-bottom:1px solid"><b>&nbsp;&nbsp;&nbsp;&nbsp;Sub Total</b></td>
				<td style="border-left:1px solid;border-top:1px solid;border-bottom:1px solid;text-align:right"><b>'.$subtotal.'</b></td>
				</tr>';	
			$kelompok=$ey['kelompok'] ;
		}	
		$subtotal+= $ey['total']  ;
		$atemps = explode( "-",$ey['nama_profesi']);
		if( count($atemps)>1 ){
			if( $group=='' OR $group<>$atemps[0]){
				$group=$atemps[0];
				$view .='<tr>
					<td>'.$group.'</td>
					<td style="border-left:1px solid">&nbsp;</td>
					</tr>';		
			}	
			$profesi='&nbsp;'.$atemps[1] ;
		}else{
			$profesi=$ey['nama_profesi'] ;
		}
		$view .='<tr>
			<td>'.$profesi.'</td>
			<td style="border-left:1px solid;text-align:right">'.$ey['total'].'</td>
			</tr>';	
	}
	$view .='<tr>
		<td style="border-top:1px solid"><b>&nbsp;&nbsp;&nbsp;&nbsp;TOTAL</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$subtotal.'</b></td>
		</tr>';		
	$view .='</table>';
// II	
	$view .='<table style="width:100%" cellspacing="0">
		<tr>
		<td><b><br>II. BERDASARKAN STATUS KARYAWAN </b></td>
		</tr></table>';	
	$view .='<table style="border:1px solid;width:100%" cellspacing="0">';
	$view .='<tr>
		<td style="border-bottom:1px solid"><b>STATUS KARYAWAN</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>LAKI-LAKI</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>PEREMPUAN</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>JUMLAH</b></td>
		</tr>';	
	$query = "SELECT d.tetap,c.kelamin,COUNT(*) as total FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id=a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id=b.biodata_id  
		INNER JOIN status_kekaryawanan d ON d.status_id=a.status_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		GROUP BY d.tetap,c.kelamin" ;
	$result = my_query($query);

	$tetap_laki=$tetap_perempuan=$tidak_tetap_laki=$tidak_tetap_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['tetap']=="ya"){
			if( $ey['kelamin']=="laki-laki"){
				$tetap_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$tetap_perempuan+=$ey['total'] ;
			}
		}elseif( $ey['tetap']=="tidak"){
			if( $ey['kelamin']=="laki-laki"){
				$tidak_tetap_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$tidak_tetap_perempuan+=$ey['total'] ;
			}
		}
	}

	$view .='<tr>
		<td>Tenaga Tetap</td>
		<td style="border-left:1px solid;text-align:right">'.$tetap_laki.'</td>
		<td style="border-left:1px solid;text-align:right">'.$tetap_perempuan.'</td>
		<td style="border-left:1px solid;text-align:right">'.($tetap_laki+$tetap_perempuan).'</td>
		</tr>';		
	$view .='<tr>
		<td>Tenaga Tidak Tetap (Kontrak)</td>
		<td style="border-left:1px solid;text-align:right">'.$tidak_tetap_laki.'</td>
		<td style="border-left:1px solid;text-align:right">'.$tidak_tetap_perempuan.'</td>
		<td style="border-left:1px solid;text-align:right">'.($tidak_tetap_laki+$tidak_tetap_perempuan).'</td>
		</tr>';	
	$view .='<tr>
		<td style="border-top:1px solid"><b>JUMLAH</b></td>
		<td style="border-left:1px solid;border-top:1px solid;text-align:right"><b>'.($tetap_laki+$tidak_tetap_laki).'</b></td>
		<td style="border-left:1px solid;border-top:1px solid;text-align:right"><b>'.($tetap_perempuan+$tidak_tetap_perempuan).'</b></td>
		<td style="border-left:1px solid;border-top:1px solid;text-align:right"><b>'.($tetap_laki+$tidak_tetap_laki+$tetap_perempuan+$tidak_tetap_perempuan).'</b></td>
		</tr>';				
	$view .='</table>';
// III	
	$view .='<table style="width:100%" cellspacing="0">
		<tr>
		<td><b><br>III. BERDASARKAN STATUS PERKAWINAN</b></td>
		</tr></table>';	
	$view .='<table style="border:1px solid;width:100%" cellspacing="0">';
	$view .='<tr>
		<td colspan="2" style="border-bottom:1px solid;text-align:center"><b>LAJANG</b></td>
		<td colspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>MENIKAH</b></td>
		<td rowspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>JUMLAH</b></td>
		</tr>';
	$view .='<tr>
		<td style="border-bottom:1px solid;text-align:center"><b>LAKI-LAKI</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>PEREMPUAN</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>LAKI-LAKI</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>PEREMPUAN</b></td>
		</tr>';			

	$query = "SELECT c.kelamin,c.marital,COUNT(*) as total FROM kekaryawanan a 
		INNER JOIN karyawan b ON b.karyawan_id=a.karyawan_id 
		INNER JOIN biodata c ON c.biodata_id=b.biodata_id 
		WHERE a.tmb<'{$tgl}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
		GROUP BY c.kelamin,c.marital" ;		
	$result = my_query($query);
	$lajang_laki=$lajang_perempuan=$menikah_laki=$menikah_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		if( $ey['marital']=="Belum Nikah"){
			if( $ey['kelamin']=="laki-laki"){
				$lajang_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$lajang_perempuan+=$ey['total'] ;
			}
		}else{
			if( $ey['kelamin']=="laki-laki"){
				$menikah_laki+=$ey['total'] ;
			}elseif( $ey['kelamin']=="perempuan"){
				$menikah_perempuan+=$ey['total'] ;
			}
		}
	}
	$view .='<tr>
		<td style="text-align:right">'.$lajang_laki.'</td>
		<td style="border-left:1px solid;text-align:right">'.$lajang_perempuan.'</td>
		<td style="border-left:1px solid;text-align:right">'.$menikah_laki.'</td>
		<td style="border-left:1px solid;text-align:right">'.$menikah_perempuan.'</td>
		<td style="border-left:1px solid;text-align:right">'.($lajang_laki+$lajang_perempuan+$menikah_laki+$menikah_perempuan).'</td>
		</tr>';	
	$view .='<tr>
		<td colspan="2" style="border-top:1px solid;text-align:center"><b>'.($lajang_laki+$lajang_perempuan).'</b></td>
		<td colspan="2" style="border-top:1px solid;border-left:1px solid;text-align:center"><b>'.($menikah_laki+$menikah_perempuan).'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.($lajang_laki+$lajang_perempuan+$menikah_laki+$menikah_perempuan).'</b></td>
		</tr>';	
	$view .='</table>';	
// IV		
	$view .='<table style="width:100%" cellspacing="0">
		<tr>
		<td><b><br>IV. BERDASARKAN GOLONGAN / PANGKAT</b></td>
		</tr></table>';	
	$view .='<table style="border:1px solid;width:100%" cellspacing="0">';
	$view .='<tr>
		<td style="text-align:center"><b>PANGKAT / GOLONGAN</b></td>
		<td colspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:30%"><b>JENIS KELAMIN</b></td>
		<td rowspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>JUMLAH</b></td>
		</tr>';		
	$view .='<tr>
		<td style="border-bottom:1px solid;text-align:center"><b>JABATAN</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>LAKI-LAKI</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>PEREMPUAN</b></td>
		</tr>';					
	$query = "SELECT a.pangkat_id, a.nama_pangkat,
		(SELECT COUNT(*) FROM kekaryawanan b
			INNER JOIN golongan_jabatan c ON c.golongan_jabatan_id=b.golongan_jabatan_id 
			INNER JOIN karyawan d ON d.karyawan_id=b.karyawan_id 
			INNER JOIN biodata e ON e.biodata_id=d.biodata_id  
			WHERE b.tmb<'{$tgl}' 
			AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.pangkat_id=a.pangkat_id 
			AND e.kelamin='laki-laki') as laki,
		(SELECT COUNT(*) FROM kekaryawanan b
			INNER JOIN golongan_jabatan c ON c.golongan_jabatan_id=b.golongan_jabatan_id 
			INNER JOIN karyawan d ON d.karyawan_id=b.karyawan_id 
			INNER JOIN biodata e ON e.biodata_id=d.biodata_id  
			WHERE b.tmb<'{$tgl}' 
			AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
			AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
			AND c.pangkat_id=a.pangkat_id 
			AND e.kelamin='perempuan') as perempuan 
			FROM pangkat a 
			ORDER BY a.pangkat_id";
	$result = my_query($query);
	$total_laki=$total_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		$total_laki+=$ey['laki'] ;
		$total_perempuan+=$ey['perempuan'] ;
		$view .='<tr>
			<td>'.$ey['nama_pangkat'].'</td>
			<td style="border-left:1px solid;text-align:right">'.$ey['laki'].'</td>
			<td style="border-left:1px solid;text-align:right">'.$ey['perempuan'].'</td>
			<td style="border-left:1px solid;text-align:right">'.($ey['laki']+$ey['perempuan']).'</td>
			</tr>';	
	}		
	$view .='<tr>
		<td style="border-top:1px solid;text-align:center"><b>JUMLAH</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total_laki.'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total_perempuan.'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.($total_laki+$total_perempuan).'</b></td>
		</tr>';		
	$view .='</table>';	
// V	
	$view .='<table style="width:100%" cellspacing="0">
		<tr>
		<td><b><br>V. BERDASARKAN USIA</b></td>
		</tr></table>';	
	$view .='<table style="border:1px solid;width:100%" cellspacing="0">';
	$view .='<tr>
		<td style="text-align:center"><b>USIA</b></td>
		<td colspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:30%"><b>JENIS KELAMIN</b></td>
		<td rowspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>JUMLAH</b></td>
		</tr>';		
	$view .='<tr>
		<td style="border-bottom:1px solid;text-align:center"><b>(Tahun)</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>LAKI-LAKI</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>PEREMPUAN</b></td>
		</tr>';				

	$total_laki=$total_perempuan=0;
	for( $i=20; $i<=50; $i+=5 ){
		if($i==20){
			$start=0 ;
			$label1='< ' ;
		}else{
			$start=$i ;
			$label1='> ' ;
		}
		if($i==50){
			$end=150 ;
			$label2=$i.' >' ;
		}else{
			$end=$i+5 ;
			$label2=($i+5) ;
		}	
		$laki=get_total_by_gender_age($tgl,'laki-laki', $start,$end);
		$perempuan=get_total_by_gender_age($tgl,'perempuan', $start,$end);
		$total_laki+=$laki ;
		$total_perempuan+=$perempuan ;
		$view .='<tr>
			<td>'.($label1.$i.' - '.$label2).'</td>
			<td style="border-left:1px solid;text-align:right">'.$laki.'</td>
			<td style="border-left:1px solid;text-align:right">'.$perempuan.'</td>
			<td style="border-left:1px solid;text-align:right">'.($laki+$perempuan).'</td>
			</tr>';	
	}

	$total=$total_laki+$total_perempuan ;
	$view .='<tr>
		<td style="border-top:1px solid;text-align:center"><b>JUMLAH</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total_laki.'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total_perempuan.'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total.'</b></td>
		</tr>';	
	$view .='</table>';
	if( $total>0 ){	
		$view .='<table style="width:100%" cellspacing="0">';	
		$view .='<tr>
			<td style="text-align:center">&nbsp;</td>
			<td style="border-left:1px solid;border-bottom:1px solid;text-align:right;width:15%"><b>'.(round(($total_laki/$total)*100,2).' %' ).'</b></td>
			<td style="border-left:1px solid;border-bottom:1px solid;border-right:1px solid;text-align:right;width:15%"><b>'.(round(($total_perempuan/$total)*100,2).' %').'</b></td>
			<td style="width:15%">&nbsp;</td>
			</tr>';	
		$view .='</table>';		
	}

// VI
	$view .='<table style="width:100%" cellspacing="0">
		<tr>
		<td><b><br>VI. BERDASARKAN PENDIDIKAN</b></td>
		</tr></table>';	
	$view .='<table style="border:1px solid;width:100%" cellspacing="0">';
	$view .='<tr>
		<td style="text-align:center"><b>PENDIDIKAN</b></td>
		<td colspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:30%"><b>JENIS KELAMIN</b></td>
		<td rowspan="2" style="border-left:1px solid;border-bottom:1px solid;text-align:center;width:15%"><b>JUMLAH</b></td>
		</tr>';		
	$view .='<tr>
		<td style="border-bottom:1px solid;text-align:center"><b>(Tahun)</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>LAKI-LAKI</b></td>
		<td style="border-left:1px solid;border-bottom:1px solid;text-align:center"><b>PEREMPUAN</b></td>
		</tr>';	 		
	$query = "
		SELECT a.code, SUM(
			(SELECT COUNT(h.karyawan_id) 
			FROM (
				SELECT b.karyawan_id,d.kelamin, MAX( g.level_pendidikan_id ) AS	LEVEL FROM kekaryawanan b
				INNER JOIN karyawan c ON c.karyawan_id = b.karyawan_id
				INNER JOIN biodata d ON d.biodata_id = c.biodata_id
				INNER JOIN riwayat_pendidikan_karyawan e ON e.karyawan_id = b.karyawan_id
				INNER JOIN riwayat_pendidikan f ON f.riwayat_pendidikan_id = e.riwayat_pendidikan_id
				INNER JOIN pendidikan g ON g.pendidikan_id = f.pendidikan_id
				WHERE b.tmb<'{$tgl}' 
				AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
				GROUP BY b.karyawan_id
				) AS h
				WHERE h.level = a.level_pendidikan_id
				AND  h.kelamin='laki-laki')) as laki, SUM(
			(SELECT COUNT(h.karyawan_id) 
			FROM (
				SELECT b.karyawan_id,d.kelamin, MAX( g.level_pendidikan_id ) AS	LEVEL FROM kekaryawanan b
				INNER JOIN karyawan c ON c.karyawan_id = b.karyawan_id
				INNER JOIN biodata d ON d.biodata_id = c.biodata_id
				INNER JOIN riwayat_pendidikan_karyawan e ON e.karyawan_id = b.karyawan_id
				INNER JOIN riwayat_pendidikan f ON f.riwayat_pendidikan_id = e.riwayat_pendidikan_id
				INNER JOIN pendidikan g ON g.pendidikan_id = f.pendidikan_id
				WHERE b.tmb<'{$tgl}' 
				AND b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable) 
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian where TMT<'{$tgl}') 
				GROUP BY b.karyawan_id
				) AS h
				WHERE h.level = a.level_pendidikan_id
				AND  h.kelamin='perempuan')) as perempuan 		
		FROM level_pendidikan a	
		GROUP BY a.code ORDER BY a.level_pendidikan_id DESC ";
	$result = my_query($query);
	$total_laki=$total_perempuan=0;
	while( $ey = my_fetch_array($result) ){
		$total_laki+=$ey['laki'] ;
		$total_perempuan+=$ey['perempuan'] ;
		$view .='<tr>
			<td>'.$ey['code'].'</td>
			<td style="border-left:1px solid;text-align:right">'.$ey['laki'].'</td>
			<td style="border-left:1px solid;text-align:right">'.$ey['perempuan'].'</td>
			<td style="border-left:1px solid;text-align:right">'.($ey['laki']+$ey['perempuan']).'</td>
			</tr>';	
	}
	$view .='<tr>
		<td style="border-top:1px solid;text-align:center"><b>JUMLAH</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total_laki.'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.$total_perempuan.'</b></td>
		<td style="border-top:1px solid;border-left:1px solid;text-align:right"><b>'.($total_laki+$total_perempuan).'</b></td>
		</tr>';	
	$view .='</table>';
	$view .='</table>';	
	return $view;	
}

